import pymysql


# 创建工具类
class DBTools(object):
    # 建立连接
    @staticmethod
    def __db_connect():
        # 建立连接，返回连接的对象
        # conn = pymysql.connect(host="192.168.58.131",
        #                        port=3306, user="root",
        #                        password="123456",
        #                        database="books", charset="utf8")
        conn = pymysql.connect(host="211.103.136.244",
                               port=7061, user="student",
                               password="iHRM_student_2022",
                               database="ihrm", charset="utf8")
        return conn

    # 数据库 查询
    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None

        # 执行sql语句，获取结果集
        # 返回查询的一条数据
        try:
            conn = DBTools.__db_connect()
            # 3. 创建游标
            cursor = conn.cursor()
            # 4. 执行 sql 语句
            cursor.execute(sql)
            # 查询
            ret = cursor.fetchone()
            return ret

        except Exception as err:
            print("出现异常，错误：", err)
        finally:
            # 5. 关闭游标
            cursor.close()
            # 6. 关闭连接
            conn.close()

    def query_many(self):
        pass

    # 执行sql语句，获取结果集
    # 返回查询的多条数据

    # 数据库 增删改
    @staticmethod
    def db_idu(sql):
        conn = None
        cursor = None
        try:
            conn = DBTools.__db_connect()
            # 3. 创建游标
            cursor = conn.cursor()
            # 4. 执行 sql 语句
            cursor.execute(sql)
            conn.commit()
            print("数据库操作成功")
        except Exception as err:
            print("数据库操作出现异常，错误：", err)
            conn.rollback()
        finally:
            # 5. 关闭游标
            cursor.close()
            # 6. 关闭连接
            conn.close()
    # 执行sql语句
    # 操作事务（提交、回滚）


if __name__ == '__main__':
    ret = DBTools.query_one("select * from bs_user;")
    print(ret)

    # DBTools.db_idu("insert into t_book(id,title,pub_date) values(4,'西游记','1986-01-01');")
    # DBTools.db_idu("update t_book set `read` = `read`+100 where title = '西游记';")
    # DBTools.db_idu("delete from t_book where id = 4;")




